package com.gack.business.dao;

import java.util.List;

import javax.persistence.EntityManager;
import javax.persistence.Query;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Repository;
/**
 * 运营后台首页基础数据统计DAO层
 * @author lsy
 *
 */

import com.gack.business.vo.IncomeStatistical;
import com.gack.business.vo.RegisterStatistical;
import com.gack.business.vo.StoresUseStatistical;
import com.gack.business.vo.VideoUseStatistical;
@Repository
public class StatisticalDao {
	@Autowired
	private EntityManager entityManager;
	/**
	 * 查询收入集合类
	 * @param year 年
	 * @param month 月
	 * @return 收入集合类
	 */
	public List<IncomeStatistical> findIncomeStatistical(Integer year, Integer month) {
		List<IncomeStatistical> incomeStatisticals = null;
		String jpql = "SELECT new com.gack.business.vo.IncomeStatistical(date_format(o.endTime,'%m'),sum(o.amount) ) FROM Order o WHERE YEAR(o.endTime) =:year AND MONTH(o.endTime) =:month AND o.status IN(8,9,10) GROUP BY date_format(o.endTime,'%m')";
		Query query = entityManager.createQuery(jpql);
		query.setParameter("year", year);
		query.setParameter("month", month);
		incomeStatisticals = query.getResultList();
		return incomeStatisticals;
	}
	/**
	 * 查询注册统计类
	 * @param year 年
	 * @param month 月
	 * @param channel 渠道 android ios pc
	 * @return
	 */
	public List<RegisterStatistical> findRegisterStatistical(Integer year, Integer month, String channel) {
		List<RegisterStatistical>  registerStatisticals = null;
		String jpql = "SELECT new com.gack.business.vo.RegisterStatistical(date_format(u.createtime,'%m'),count(id)) FROM User u WHERE YEAR(u.createtime) =:year AND MONTH(u.createtime) =:month AND u.registerSource=:channel  GROUP BY date_format(u.createtime,'%m')";
		Query query = entityManager.createQuery(jpql);
		query.setParameter("year", year);
		query.setParameter("month", month);
		query.setParameter("channel", channel);
		registerStatisticals = query.getResultList();
		return registerStatisticals;
	}
	/**
	 * 查询门店使用情况，按照小时统计
	 * @param date 日期 eg:2018-09-14
	 * @param hour 24小时
	 * @return
	 */
	public List<StoresUseStatistical> findStoresUseStatistical(String date, Integer hour) {
		List<StoresUseStatistical> statisticals = null;
		String jpql = "SELECT new com.gack.business.vo.StoresUseStatistical(date_format(o.beginTime,'%H'),count(id)) FROM Order o WHERE  o.status NOT IN (1,2,3) AND date_format(o.beginTime,'%Y-%m-%d')=:date AND hour(o.beginTime)=:hour  group by date_format(o.beginTime,'%H')";
		Query query = entityManager.createQuery(jpql);
		query.setParameter("date", date);
		query.setParameter("hour", hour);
		statisticals = query.getResultList();
		return statisticals;
	}
	/**
	 * 查询视频使用情况，按照小时统计
	 * @param date 日期 eg:2018-09-14
	 * @param hour 24小时
	 * @return
	 */
	public List<VideoUseStatistical> findVideoUseStatistical(String date, Integer hour, Integer channel) {
		String jpql = "SELECT new com.gack.business.vo.VideoUseStatistical(date_format(j.joinTime,'%H'),count(id)) FROM JoinConferenceRecord j WHERE date_format(j.joinTime,'%Y-%m-%d')=:date AND j.channel=:channel AND hour(j.joinTime)=:hour  group by date_format(j.joinTime,'%H')";
		List<VideoUseStatistical> videoUseStatisticals = null;
		Query query = entityManager.createQuery(jpql);
		query.setParameter("date", date);
		query.setParameter("hour", hour);
		query.setParameter("channel", channel);
		videoUseStatisticals = query.getResultList();
		return videoUseStatisticals;
	}
	
	

}
